﻿using Anke.SHManage.IDAL;
using Anke.SHManage.Model;
using Anke.SHManage.Utility;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity.Validation;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Anke.SHManage.MSSQLDAL
{
    public partial class I_ApplyDAL
    {

        IDALContext dalContext = new DALContextFactory().GetDALContext();
        AKV7ManageEntities db = DBContextFactory.GetDBContext() as AKV7ManageEntities;

        /// <summary>
        /// 申请操作
        /// </summary>
        /// <param name="apply">申请主表</param>
        /// <param name="applyDetailInfos">申请从表</param>
        /// <param name="errorMsg">错误信息</param>
        /// <returns></returns>
        public bool ApplyOperate(I_Apply apply, List<I_ApplyDetail> applyDetailInfos, ref string errorMsg)
        {
            //创建事务
            using (var tran = db.Database.BeginTransaction())
            {
                try
                {
                    //1 先插入主表
                    dalContext.II_ApplyDAL.Add(apply);

                    foreach (I_ApplyDetail info in applyDetailInfos)
                    {
                        //2. 再插入从表信息 
                        dalContext.II_ApplyDetailDAL.Add(info);
                    }

                    tran.Commit(); //提交事务

                    return true;
                }
                catch (DbEntityValidationException dbEx)
                {
                    if (tran != null)
                        tran.Rollback();  //回滚事务

                    errorMsg = "申请操作失败 失败！  原因:" + dbEx.EntityValidationErrors.FirstOrDefault().ValidationErrors.FirstOrDefault().ErrorMessage;
                    return false;
                }
                catch (Exception ex)
                {
                    if (tran != null)
                        tran.Rollback();  //回滚事务
                    errorMsg = "申请操作失败！ 操作已取消！~~" + ex.InnerException.Message;
                    return false;
                }
            }
        }



        /// <summary>
        /// 获取申请列表
        /// </summary>
        /// <param name="page"></param>
        /// <param name="rows"></param>
        /// <param name="rowCounts"></param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <param name="EntryCode"></param>
        /// <param name="EntryType"></param>
        /// <returns></returns>
        public object GetApplyList(int page, int rows, ref int rowCounts, DateTime startTime, DateTime endTime, string ApplyUserID, string ApplyType, string centerID, List<int> listUserStorage)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"  
                 select  distinct 

                         identity(int,1,1) as 行号,
                         ApplyCode = a.ApplyCode,
                         ApplyReceivingStoreID = a.ApplyReceivingStoreID,
                         ApplyTime = a.ApplyTime,
                         ApplyType = a.ApplyType,
                         ApplyUserID = a.ApplyUserID,
                         ApprovalTime = a.ApprovalTime,
                         ApprovalUserID = a.ApprovalUserID,
                         Remark = a.Remark,
                         ApplyUserName = p1.Name
                         ,ApprovalUserName = p2.Name  
                         ,ApplyTypeName = td.Name
                         ,ApplyReceivingStoreName = s.Name  

                          into #temp
						from  I_Apply a 
						left join  P_User  p1  on  a.ApplyUserID = p1.ID
						left join  P_User  p2  on  a.ApprovalUserID = p2.ID
						left join P_Department dep on p1.DepID = dep.ID
						left join TDictionary td on a.ApplyType = td.ID
						left join I_Storage  s on a.ApplyReceivingStoreID  = s.StorageID  
						left join  I_ApplyDetail ad on ad.ApplyCode  =a.ApplyCode
						left join  I_Material im on ad.MaterialID = im.ID
                         where 1= 1    ");
            WhereClauseUtility.AddDateTimeGreaterThan("a.ApplyTime", startTime, sb);
            WhereClauseUtility.AddDateTimeLessThan("a.ApplyTime", endTime, sb);
            if (!string.IsNullOrEmpty(ApplyUserID))
            {
                int usrID = int.Parse(ApplyUserID);
                WhereClauseUtility.AddIntEqual("a.ApplyUserID", usrID, sb);
            }
            if (!string.IsNullOrEmpty(ApplyType))
            {
                WhereClauseUtility.AddStringEqual("a.ApplyType", ApplyType, sb);
            }
            //新增 分中心 (如果是分中心权限进来了，就取申领人所在的分中心的记录并且申领物资为贵重药品的)  2016-4-25
            if (!string.IsNullOrEmpty(centerID) && centerID != "-1")
            {
                //ApplyUserID 所在的部门所属的分中心  2016-4-25
                WhereClauseUtility.AddStringEqual("dep.DispatchSubCenterID", centerID, sb);
                //同时控制为只能看贵重药品 2016-4-25
                sb.Append("and im.MTypeID = 'MaterialType-15'  ");
            }

            //根据人员仓库关系里的所能控制的仓库 才能看见 可以审批的仓库（他人来申领东西的仓库）  2016-5-23
            if (listUserStorage != null)
            {
                if (listUserStorage.Count > 0)
                {
                    string liststring = "";
                    for (int i = 0; i < listUserStorage.Count; i++)
                    {

                        liststring = liststring + listUserStorage[i].ToString() + ",";
                    }
                    string StorageList = "(" + liststring.Substring(0, liststring.Length - 1) + ")";

                    sb.Append("and ad.ApplyTargetStorageCode in    ").Append(StorageList);

                }
            }
            sb.Append(@" select top " + rows + " A.*  from #temp A where  行号>" + (page - 1) * rows + "  order by 行号 ");
            sb.Append(@"  SELECT count(*) FROM #temp t   drop table #temp ");


            List<I_ApplyExt> list = new List<I_ApplyExt>();
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {

                    I_ApplyExt info = new I_ApplyExt();

                    info.ApplyCode = dr["ApplyCode"].ToString();
                    info.ApplyReceivingStoreID = Convert.ToInt32(dr["ApplyReceivingStoreID"].ToString());
                    info.ApplyTime = Convert.ToDateTime(dr["ApplyTime"].ToString());
                    info.ApplyType = dr["ApplyType"].ToString();
                    info.ApplyUserID = Convert.ToInt32(dr["ApplyUserID"].ToString());
                    info.ApprovalTime = Utility.DBConvert.ConvertDBTypeToNullable(dr["ApprovalTime"]);
                    info.ApprovalUserID = Utility.DBConvert.ConvertDBTypeToInt(dr["ApprovalUserID"]);
                    info.Remark = dr["Remark"].ToString();
                    info.ApplyUserName = dr["ApplyUserName"].ToString();
                    info.ApprovalUserName = dr["ApprovalUserName"].ToString();

                    info.ApplyTypeName = dr["ApplyTypeName"].ToString();
                    info.ApplyReceivingStoreName = dr["ApplyReceivingStoreName"].ToString();
                    list.Add(info);
                }
            }
            rowCounts = Convert.ToInt32(ds.Tables[1].Rows[0][0]);

            return list;

            //var q = (from a in db.I_Apply
            //         select new
            //         {
            //             ApplyCode = a.ApplyCode,
            //             ApplyReceivingStoreID = a.ApplyReceivingStoreID,
            //             ApplyTime = a.ApplyTime,
            //             ApplyType = a.ApplyType,
            //             ApplyUserID = a.ApplyUserID,
            //             ApprovalTime = a.ApprovalTime,
            //             ApprovalUserID = a.ApprovalUserID,
            //             Remark = a.Remark,

            //             ApplyUserName = a.P_User.Name, //用导航属性 申请人姓


            //             ApprovalUserName = a.P_User1.Name,  //批准人姓名
            //             ApplyTypeName = a.TDictionary.Name,   //申请类型
            //             ApplyReceivingStoreName = a.I_Storage.Name   //接受仓库名称
            //         });

            //q = q.Where(a => a.ApplyTime >= startTime && a.ApplyTime <= endTime);




            //if (!string.IsNullOrEmpty(ApplyUserID))
            //{
            //    int usrID = int.Parse(ApplyUserID);
            //    q = q.Where(a => a.ApplyUserID == usrID);
            //}
            //if (!string.IsNullOrEmpty(ApplyType))
            //{
            //    q = q.Where(a => a.ApplyType == ApplyType);
            //}

            //rowCounts = q.Count();
            //var r = q.OrderBy(a => a.ApplyTime).Skip((page - 1) * rows).Take(rows).ToList();
            //return r;
        }

        //物资申领单查询 2016-09-02 yh
        public object GetApplyOrderList(int page, int rows, ref int rowCounts, DateTime startTime, DateTime endTime, string ApplyUserID, string ApplyType, string centerID, List<int> listUserStorage)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"  
                 select  distinct 
                         identity(int,1,1) as 行号,
                         ApplyCode = a.ApplyCode,
                         ApplyReceivingStoreID = a.ApplyReceivingStoreID,
                         ApplyTime = a.ApplyTime,
                         ApplyType = a.ApplyType,
                         ApplyUserID = a.ApplyUserID,
                         ApprovalTime = a.ApprovalTime,
                         ApprovalUserID = a.ApprovalUserID,
                         Remark = a.Remark,
                         ApplyUserName = p1.Name
                         ,ApprovalUserName = p2.Name  
                         ,ApplyTypeName = td.Name
                         ,ApplyReceivingStoreName = s.Name  
                        into #temp
						from  I_Apply a 
						left join  P_User  p1  on  a.ApplyUserID = p1.ID
						left join  P_User  p2  on  a.ApprovalUserID = p2.ID
						left join P_Department dep on p1.DepID = dep.ID
						left join TDictionary td on a.ApplyType = td.ID
						left join I_Storage  s on a.ApplyReceivingStoreID  = s.StorageID  
						left join  I_ApplyDetail ad on ad.ApplyCode  =a.ApplyCode
						left join  I_Material im on ad.MaterialID = im.ID
                        where 1= 1    ");
            WhereClauseUtility.AddDateTimeGreaterThan("a.ApplyTime", startTime, sb);
            WhereClauseUtility.AddDateTimeLessThan("a.ApplyTime", endTime, sb);
            if (!string.IsNullOrEmpty(ApplyUserID))
            {
                int usrID = int.Parse(ApplyUserID);
                WhereClauseUtility.AddIntEqual("a.ApplyUserID", usrID, sb);
            }
            if (!string.IsNullOrEmpty(ApplyType))
            {
                WhereClauseUtility.AddStringEqual("a.ApplyType", ApplyType, sb);
            }
            ////新增 分中心 (如果是分中心权限进来了，就取申领人所在的分中心的记录) 
            //if (!string.IsNullOrEmpty(centerID) && centerID != "-1")
            //{
            //    //ApplyUserID 所在的部门所属的分中心  2016-4-25
            //    WhereClauseUtility.AddStringEqual("dep.DispatchSubCenterID", centerID, sb);
            //}
            //根据人员仓库关系里的所能控制的仓库 才能看见 可以审批的仓库（他人来申领东西的仓库）
            if (listUserStorage != null)
            {
                if (listUserStorage.Count > 0)
                {
                    string liststring = "";
                    for (int i = 0; i < listUserStorage.Count; i++)
                    {
                        liststring = liststring + listUserStorage[i].ToString() + ",";
                    }
                    string StorageList = "(" + liststring.Substring(0, liststring.Length - 1) + ")";

                    sb.Append("and a.ApplyReceivingStoreID  in").Append(StorageList);
                }
            }
            sb.Append(@" select top " + rows + " A.*  from #temp A where  行号>" + (page - 1) * rows + "  order by 行号 ");
            sb.Append(@"  SELECT count(*) FROM #temp t   drop table #temp ");

            List<I_ApplyExt> list = new List<I_ApplyExt>();
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    I_ApplyExt info = new I_ApplyExt();
                    info.ApplyCode = dr["ApplyCode"].ToString();
                    info.ApplyReceivingStoreID = Convert.ToInt32(dr["ApplyReceivingStoreID"].ToString());
                    info.ApplyTime = Convert.ToDateTime(dr["ApplyTime"].ToString());
                    info.ApplyType = dr["ApplyType"].ToString();
                    info.ApplyUserID = Convert.ToInt32(dr["ApplyUserID"].ToString());
                    info.ApprovalTime = Utility.DBConvert.ConvertDBTypeToNullable(dr["ApprovalTime"]);
                    info.ApprovalUserID = Utility.DBConvert.ConvertDBTypeToInt(dr["ApprovalUserID"]);
                    info.Remark = dr["Remark"].ToString();
                    info.ApplyUserName = dr["ApplyUserName"].ToString();
                    info.ApprovalUserName = dr["ApprovalUserName"].ToString();
                    info.ApplyTypeName = dr["ApplyTypeName"].ToString();
                    info.ApplyReceivingStoreName = dr["ApplyReceivingStoreName"].ToString();
                    list.Add(info);
                }
            }
            rowCounts = Convert.ToInt32(ds.Tables[1].Rows[0][0]);

            return list;
        }

        public List<I_ApplyDetailExt> getApplyDetailListBy(string strApplyCode)
        {
            string sql = @"SELECT ApplyDetailCode
                          ,ApplyCode
                          ,apd.MaterialID
                          ,apd.RealBatchNo
                          ,apd.BatchNo
                          ,apd.ApplyTime
                          ,apd.ApplyCounts
                          ,apd.ApplyUserID
                          ,apd.SelfStorageCode
                          ,apd.ApplyTargetStorageCode
                          ,apd.Remark
                          ,apd.ApprovalCounts
                          ,sur.Surplus
	                      ,sur.ValidityDate
	                      ,MaterialName=mat.Name
	                      ,ApplyUserName=usr.Name
	                      ,SelfStorageName=selfsto.Name
	                      ,ApplyTargetStorageName = targetsto.Name
                          FROM I_ApplyDetail apd
                          LEFT JOIN I_Surplus sur on apd.MaterialID= sur.MaterialID and apd.ApplyTargetStorageCode=sur.StorageCode  and apd.BatchNo=sur.BatchNo  and apd.RealBatchNo = sur.RealBatchNo
                          LEFT JOIN I_Material mat on mat.ID =apd.MaterialID
                          LEFT JOIN P_User usr on usr.ID = apd.ApplyUserID
                          LEFT JOIN I_Storage selfsto on selfsto.StorageID = apd.SelfStorageCode
                          LEFT JOIN I_Storage targetsto on targetsto.StorageID =  apd.ApplyTargetStorageCode
                          WHERE apd.ApplyCode='" + strApplyCode + "'";

            List<I_ApplyDetailExt> list = db.Database.SqlQuery<I_ApplyDetailExt>(sql).ToList();
            return list;
        }


        /// <summary>
        /// 同意申请
        /// </summary>
        /// <param name="apply"></param>
        /// <param name="applyDetail"></param>
        /// <param name="deliveryInfo"></param>
        /// <param name="deliveryDetailInfos"></param>
        /// <param name="errorMsg"></param>
        /// <returns></returns>
        public bool ApproveApplyOperate(I_Apply apply, List<I_ApplyDetail> applyDetail, I_Delivery deliveryInfo, List<I_DeliveryDetail> deliveryDetailInfos, ref string errorMsg)
        {
            //创建事务
            using (var tran = db.Database.BeginTransaction())
            {
                try
                {
                    //1.0 更新申请表
                    new I_ApplyDAL().Modify(apply, "ApplyType", "ApprovalUserID", "ApprovalTime");
                    foreach (var info in applyDetail)
                    {
                        new I_ApplyDetailDAL().Modify(info, "ApprovalCounts");
                    }

                    //2.0 做出库操作
                    #region 出库操作
                    
                    //2018-12-11 zch
                    int id = 1;
                    I_Delivery maxdel = (from e in db.I_Delivery
                                         orderby e.DeliveryTime descending
                                         where e.DeliveryTime.Year == deliveryInfo.DeliveryTime.Year && e.DeliveryTime.Month == deliveryInfo.DeliveryTime.Month
                                         && e.DeliveryCode.Contains("SJOUT")
                                         select e).FirstOrDefault();
                    if (maxdel != null)
                    {
                        id = Convert.ToInt32(maxdel.DeliveryCode.Substring(maxdel.DeliveryCode.Length - 4, 4));
                        id++;
                    }
                    deliveryInfo.DeliveryCode = "SJOUT" + deliveryInfo.DeliveryTime.ToString("yyyyMM") + id.ToString().PadLeft(4, '0'); //四位数不够补0;
                    //end

                    //1 先插入出库主表
                    dalContext.II_DeliveryDAL.Add(deliveryInfo);

                    int ind = 0;
                    foreach (I_DeliveryDetail info in deliveryDetailInfos)
                    {  //2018-12-11 zch
                        ind++;
                        info.DeliveryCode = deliveryInfo.DeliveryCode;
                        info.DeliveryDetailCode = info.DeliveryCode + ind;
                        info.TargetEntryDetailCode = info.DeliveryDetailCode;
                        //end

                        //2. 再插入从表信息 
                        dalContext.II_DeliveryDetailDAL.Add(info);

                        //3. 保存剩余流水表 
                        dalContext.II_SurplusDAL.SaveDeliverySurplusInfo(info, deliveryInfo.DeliveryType, ref errorMsg);
                    }

                    #endregion

                    tran.Commit(); //提交事务

                    return true;
                }
                catch (Exception ex)
                {
                    if (tran != null)
                        tran.Rollback();  //回滚事务
                    errorMsg = "同意申请操作失败！ 操作已取消！~~" + ex.Message;
                    return false;
                }
            }
        }
    }
}
